﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.IRepository.ICargo_mentRepository;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Cargo_mentRepository
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Cargo_mentRepository : ICargo_mentRepository
    {
        public Cargo_mentRepository(MyDbContext myDbContext)
        {
            MyDbContext = myDbContext;
        }

        public MyDbContext MyDbContext { get; }

        public int Count(string time, string Cg_name)
        {
            string sql = $@"select * from [dbo].[Cargo_ment] where Is_del='0'";
            if (time != null && null != "")
            {
                string[] time1 = time.Split(',');
                sql += $@" and [Crete_time]>='{time1[0]}' and [Crete_time]<='{time1[1]}'";
            }
            if (Cg_name != null && Cg_name != "")
            {
                sql += $@" and [Cg_name] like '%{Cg_name}%'";
            }
            return MyDbContext.Database.GetDbConnection().Query(sql).Count();
        }

        public int Delete(int Cg_id)
        {
            string sql = $@"update [dbo].[Cargo_ment] set Is_del='1' where Cg_id='{Cg_id}'";
            return MyDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Cargo_ment> GetCargo_ment_time(string time, string Cg_name, int currentPage, int PageSize)
        {
            string sql = "select * from [dbo].[Cargo_ment] where Is_del='0'";
            if (time != null && null != "")
            {
                string[] time1 = time.Split(',');
                sql += $@" and [Crete_time]>='{time1[0]}' and [Crete_time]<='{time1[1]}'";
            }
            if (Cg_name != null && Cg_name != "")
            {
                sql += $@" and [Cg_name] like '%{Cg_name}%'";
            }
            List<Cargo_ment> cargo_Ments = MyDbContext.Database.GetDbConnection().Query<Cargo_ment>(sql).ToList();
            var list = cargo_Ments.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return list;
        }

        public List<Cargo_ment> GetCargo_s(int currentPage, int PageSize)
        {
            string sql = $@"select * from [dbo].[Cargo_ment] where Is_del='0'";
            List<Cargo_ment> list = MyDbContext.Database.GetDbConnection().Query<Cargo_ment>(sql).ToList();
            var cargolist = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return cargolist;
        }

        public int Add(Cargo_ment cargo_Ment)
        {
            string sql = $@"INSERT INTO [dbo].[Cargo_ment]([Cg_name],[Cg_length],[Cg_width],[Cg_height],[Cg_volume],[Cg_weight],[Cg_value],[Cg_unit],[Cg_status],[Cg_remarks]
                            ,[Crete_by],[Crete_time],[Update_by],[Update_time],[Is_del])
                            VALUES('{cargo_Ment.Cg_name}','{cargo_Ment.Cg_length}','{cargo_Ment.Cg_width}','{cargo_Ment.Cg_height}','{cargo_Ment.Cg_volume}',
                           '{cargo_Ment.Cg_weight}','{cargo_Ment.Cg_value}',
                           '{cargo_Ment.Cg_unit}','{cargo_Ment.Cg_status}','{cargo_Ment.Cg_remarks}','{cargo_Ment.Crete_by}','{cargo_Ment.Crete_time}',
                           '{cargo_Ment.Update_by}','{cargo_Ment.Update_time}','{cargo_Ment.Is_del}')";
            return MyDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Cargo_ment> GetCargo_Ments_id(int Cg_id)
        {
            string sql = $@"select * from [dbo].[Cargo_ment] where [Cg_id]='{Cg_id}'";
            List<Cargo_ment> carrier_Ments = MyDbContext.Database.GetDbConnection().Query<Cargo_ment>(sql).ToList();
            return carrier_Ments;
        }

        public int Update(Cargo_ment cargo_ment)
        {
            string sql = $@"UPDATE [dbo].[Cargo_ment]
                            SET [Cg_name] = '{cargo_ment.Cg_name}',[Cg_length] = '{cargo_ment.Cg_length}',[Cg_width] = '{cargo_ment.Cg_width}',[Cg_height] = '{cargo_ment.Cg_height}',
                                [Cg_volume] = '{cargo_ment.Cg_volume}',[Cg_weight] = '{cargo_ment.Cg_weight}',[Cg_value] = '{cargo_ment.Cg_value}'
                               ,[Cg_unit] = '{cargo_ment.Cg_unit}',[Cg_status] = '{cargo_ment.Cg_status}',[Cg_remarks] = '{cargo_ment.Cg_remarks}',[Crete_by] = '{cargo_ment.Crete_by}',
                                [Crete_time] = '{cargo_ment.Crete_time}',[Update_by] = '{cargo_ment.Update_by}',[Update_time] = '{cargo_ment.Update_time}',[Is_del] = '{cargo_ment.Is_del}'
                                WHERE Cg_id='{cargo_ment.Cg_id}'";
            return MyDbContext.Database.GetDbConnection().Execute(sql);
        }

    }
}
